﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    public class NguyenVatLieuDAO
    {
        public static List<NguyenVatLieuDTO> SelectAll()
        {
            List<NguyenVatLieuDTO> list = new List<NguyenVatLieuDTO>();
            String strSql = "select * from NguyenVatLieu";

            try
            {
                DataTable dt = SqlDataAccessHelper.ExcuteQuery(strSql);
                foreach (DataRow dr in dt.Rows)
                {
                    NguyenVatLieuDTO nvl = new NguyenVatLieuDTO();
                    nvl.MaNVL = int.Parse(dr["MaNVL"].ToString());
                    nvl.TenNVL = dr["TenNVL"].ToString();
                    nvl.DonViTinh = dr["DonViTinh"].ToString();
                    nvl.SoLuong = int.Parse(dr["SoLuong"].ToString());
                    list.Add(nvl);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static List<NguyenVatLieuDTO> Select_by_MaNCC(int MaNCC)
        {
            List<NguyenVatLieuDTO> list = new List<NguyenVatLieuDTO>();
            String strSql = "select nvl.MaNVL,ID,TenNVL,DonGia,DonViTinh from NguyenVatLieu nvl,CungCap cc " +
                "where nvl.MaNVL=cc.NVL and NCC=" + MaNCC;

            try
            {
                DataTable dt = SqlDataAccessHelper.ExcuteQuery(strSql);
                foreach (DataRow dr in dt.Rows)
                {
                    NguyenVatLieuDTO nvl = new NguyenVatLieuDTO();
                    nvl.ID_NCC_NVL = int.Parse(dr["ID"].ToString());
                    nvl.MaNVL = int.Parse(dr["MaNVL"].ToString());
                    nvl.TenNVL = dr["TenNVL"].ToString();
                    nvl.DonViTinh = dr["DonViTinh"].ToString();
                    string tmp = dr["DonGia"].ToString();
                    tmp = tmp.Substring(0, tmp.IndexOf('.'));
                    string dongia = "";
                    while (tmp.Length > 3)
                    {
                        dongia += "." + tmp.Substring(tmp.Length - 3);
                        tmp = tmp.Substring(0, tmp.Length - 3);
                    }
                    dongia = tmp + dongia ;
                    nvl.DonGia = dongia;
                    list.Add(nvl);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static List<NguyenVatLieuDTO> Select_NVL_not_exits_in_NCC(int MaNCC)
        {
            List<NguyenVatLieuDTO> list = new List<NguyenVatLieuDTO>();
            String strSql = "select MaNVL,TenNVL from NguyenVatLieu nvl where MaNVL not in (select NVL from CungCap where NCC=" + MaNCC + ")";

            try
            {
                DataTable dt = SqlDataAccessHelper.ExcuteQuery(strSql);
                foreach (DataRow dr in dt.Rows)
                {
                    NguyenVatLieuDTO nvl = new NguyenVatLieuDTO();
                    nvl.MaNVL = int.Parse(dr["MaNVL"].ToString());
                    nvl.TenNVL = dr["TenNVL"].ToString();
                    list.Add(nvl);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return list;
        }

        public static bool Delete_NVL_from_NCC(int ID_NCC_NVL)
        {
            bool result = false;
            string strSQLEmployee = "delete from CungCap where ID=@ID";
            try
            {
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@ID", ID_NCC_NVL));
                int n = SqlDataAccessHelper.ExcuteNoneQuery(strSQLEmployee, sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool Insert_NVL_for_NCC(int NVL,int NCC,string DonGia)
        {
            bool result = false;

            string strSQLEmployee = "Insert into CungCap(NVL,NCC,DonGia) values(@NVL,@NCC,@DonGia)";

            try
            {
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@NVL", NVL));
                sqlParams.Add(new SqlParameter("@NCC", NCC));
                sqlParams.Add(new SqlParameter("@DonGia", DonGia));
                int n = SqlDataAccessHelper.ExcuteNoneQuery(strSQLEmployee, sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool Insert(NguyenVatLieuDTO nvl)
        {
            bool result = false;

            string strSQLEmployee = "Insert into NguyenVatLieu(TenNVL,DonViTinh,SoLuong) " +
                "values (@Ten,@Don,@So)";

            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@Ten", nvl.TenNVL));
                sqlParams.Add(new SqlParameter("@Don", nvl.DonViTinh));
                sqlParams.Add(new SqlParameter("@So", nvl.SoLuong));
                // Call execute none query

                int n = SqlDataAccessHelper.ExcuteNoneQuery(strSQLEmployee, sqlParams);//.ExecuteNoneQuery(strSQLEmployee, sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool Delete(int MaNVL)
        {
            bool result = false;
            string strSQLEmployee = "DELETE FROM NguyenVatLieu WHERE MaNVL=@ID";
            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@ID", MaNVL));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExcuteNoneQuery(strSQLEmployee, sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool Update(NguyenVatLieuDTO nvl)
        {
            bool result = false;

            string strSQLEmployee = "Update NguyenVatLieu set TenNVL=@Ten, DonViTinh=@Don, SoLuong=@So " +
                "Where MaNVL=@ID";

            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@ID", nvl.MaNVL));
                sqlParams.Add(new SqlParameter("@Ten", nvl.TenNVL));
                sqlParams.Add(new SqlParameter("@Don", nvl.DonViTinh));
                sqlParams.Add(new SqlParameter("@So", nvl.SoLuong));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExcuteNoneQuery(strSQLEmployee, sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool Insert_SoLuong(NguyenVatLieuDTO nvl)
        {
            bool result = false;

            string strSQLEmployee = "update NguyenVatLieu set SoLuong=SoLuong+@SoLuong where MaNVL=@ID";

            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@ID", nvl.MaNVL));
                sqlParams.Add(new SqlParameter("@SoLuong", nvl.SoLuong));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExcuteNoneQuery(strSQLEmployee, sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }

        public static bool Update_SoLuong(NguyenVatLieuDTO nvl)
        {
            bool result = false;

            string strSQLEmployee = "Update NguyenVatLieu set SoLuong=@So " +
                "Where MaNVL=@ID";

            try
            {
                // Create List Sql Parameter
                List<SqlParameter> sqlParams = new List<SqlParameter>();
                sqlParams.Add(new SqlParameter("@ID", nvl.MaNVL));
                sqlParams.Add(new SqlParameter("@So", nvl.SoLuong));
                // Call Store Procedure
                int n = SqlDataAccessHelper.ExcuteNoneQuery(strSQLEmployee, sqlParams);
                if (n == 1)
                    result = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return result;
        }
    }
}
